{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AdventureWorks - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import Window\n",
    "\n",
    "sc = (SparkSession.builder.appName('app14-2') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "cust_aw = sc.read.table('sqlzoo.CustomerAW')\n",
    "cust_addr = sc.read.table('sqlzoo.CustomerAddress')\n",
    "addr = sc.read.table('sqlzoo.Address')\n",
    "product = sc.read.table('sqlzoo.Product')\n",
    "order_det = sc.read.table('sqlzoo.SalesOrderDetail')\n",
    "order_head = sc.read.table('sqlzoo.SalesOrderHeader')\n",
    "prod_model = sc.read.table('sqlzoo.ProductModel')\n",
    "prod_model_prod = sc.read.table('sqlzoo.ProductModelProductDescription')\n",
    "prod_desc = sc.read.table('sqlzoo.ProductDescription')\n",
    "prod_cat = sc.read.table('sqlzoo.ProductCategory')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "A \"Single Item Order\" is a customer order where only one item is ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SalesOrderID</th>\n",
       "      <th>UnitPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71796</td>\n",
       "      <td>31.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>71815</td>\n",
       "      <td>202.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>71784</td>\n",
       "      <td>200.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71797</td>\n",
       "      <td>202.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71782</td>\n",
       "      <td>31.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>71899</td>\n",
       "      <td>26.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>71832</td>\n",
       "      <td>26.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>71845</td>\n",
       "      <td>26.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>71845</td>\n",
       "      <td>54.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>71832</td>\n",
       "      <td>37.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>82 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    SalesOrderID  UnitPrice\n",
       "0          71796      31.58\n",
       "1          71815     202.33\n",
       "2          71784     200.05\n",
       "3          71797     202.33\n",
       "4          71782      31.58\n",
       "..           ...        ...\n",
       "77         71899      26.72\n",
       "78         71832      26.72\n",
       "79         71845      26.72\n",
       "80         71845      54.89\n",
       "81         71832      37.25\n",
       "\n",
       "[82 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(order_det.filter(col('OrderQty')==1)\n",
    " .groupBy('SalesOrderID', 'UnitPrice')\n",
    " .agg(count('SalesOrderDetailID').alias('n'))\n",
    " .filter(col('n')==1)\n",
    " .select('SalesOrderID', 'UnitPrice')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Where did the racing socks go? List the product name and the CompanyName for all Customers who ordered ProductModel 'Racing Socks'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Eastside Department Store</td>\n",
       "      <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Essential Bike Works</td>\n",
       "      <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Remarkable Bike Store</td>\n",
       "      <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Remarkable Bike Store</td>\n",
       "      <td>Racing Socks, M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Riding Cycles</td>\n",
       "      <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sports Products Store</td>\n",
       "      <td>Racing Socks, M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Sports Products Store</td>\n",
       "      <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>The Bicycle Accessories Company</td>\n",
       "      <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>The Bicycle Accessories Company</td>\n",
       "      <td>Racing Socks, M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Thrifty Parts and Sales</td>\n",
       "      <td>Racing Socks, M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       CompanyName             Name\n",
       "0        Eastside Department Store  Racing Socks, L\n",
       "1             Essential Bike Works  Racing Socks, L\n",
       "2            Remarkable Bike Store  Racing Socks, L\n",
       "3            Remarkable Bike Store  Racing Socks, M\n",
       "4                    Riding Cycles  Racing Socks, L\n",
       "5            Sports Products Store  Racing Socks, M\n",
       "6            Sports Products Store  Racing Socks, L\n",
       "7  The Bicycle Accessories Company  Racing Socks, L\n",
       "8  The Bicycle Accessories Company  Racing Socks, M\n",
       "9          Thrifty Parts and Sales  Racing Socks, M"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(product.join(order_det, on='ProductID')\n",
    " .join(order_head, on='SalesOrderID')\n",
    " .join(cust_aw, on='CustomerID')\n",
    " .join(prod_model.withColumnRenamed('Name', 'ModelName'), \n",
    "       on='ProductModelID')\n",
    " .filter(col('ModelName')=='Racing Socks')\n",
    " .select('CompanyName', 'Name')\n",
    " .distinct()\n",
    " .orderBy('CompanyName')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Show the product description for culture 'fr' for product with ProductID 736."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Le cadre LL en aluminium offre une conduite co...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Description\n",
       "0  Le cadre LL en aluminium offre une conduite co..."
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(product.join(prod_model_prod, 'ProductModelID')\n",
    " .join(prod_desc, 'ProductDescriptionID')\n",
    " .filter((col('ProductID')==736) & (col('Culture').like('%fr%')))\n",
    " .select('Description')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "Use the SubTotal value in SaleOrderHeader to list orders from the largest to the smallest. For each order show the CompanyName and the SubTotal and the total weight of the order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>SubTotal</th>\n",
       "      <th>sum(Weight)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Action Bicycle Specialists</td>\n",
       "      <td>108561.83</td>\n",
       "      <td>1133911.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Metropolitan Bicycle Supply</td>\n",
       "      <td>98278.69</td>\n",
       "      <td>679588.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bulk Discount Store</td>\n",
       "      <td>88812.86</td>\n",
       "      <td>34813.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Eastside Department Store</td>\n",
       "      <td>83858.43</td>\n",
       "      <td>565638.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Riding Cycles</td>\n",
       "      <td>78029.69</td>\n",
       "      <td>504095.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Many Bikes Store</td>\n",
       "      <td>74058.81</td>\n",
       "      <td>744328.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Instruments and Parts Company</td>\n",
       "      <td>63980.99</td>\n",
       "      <td>731576.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Extreme Riding Supplies</td>\n",
       "      <td>57634.63</td>\n",
       "      <td>589939.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Trailblazing Sports</td>\n",
       "      <td>41622.05</td>\n",
       "      <td>234328.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Professional Sales and Service</td>\n",
       "      <td>39785.33</td>\n",
       "      <td>396843.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Nearby Cycle Shop</td>\n",
       "      <td>38418.69</td>\n",
       "      <td>547260.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Closest Bicycle Store</td>\n",
       "      <td>35775.21</td>\n",
       "      <td>340144.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Thrilling Bike Tours</td>\n",
       "      <td>13823.71</td>\n",
       "      <td>191855.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Paints and Solvents Company</td>\n",
       "      <td>12685.89</td>\n",
       "      <td>122609.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Remarkable Bike Store</td>\n",
       "      <td>6634.30</td>\n",
       "      <td>45103.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Engineered Bike Systems</td>\n",
       "      <td>3398.17</td>\n",
       "      <td>37420.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Sports Products Store</td>\n",
       "      <td>3324.28</td>\n",
       "      <td>53389.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Discount Tours</td>\n",
       "      <td>2980.79</td>\n",
       "      <td>14977.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Sports Store</td>\n",
       "      <td>2453.76</td>\n",
       "      <td>38354.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Coalition Bike Company</td>\n",
       "      <td>2415.67</td>\n",
       "      <td>29183.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Aerobic Exercise Company</td>\n",
       "      <td>2137.23</td>\n",
       "      <td>6770.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Tachometers and Accessories</td>\n",
       "      <td>2016.34</td>\n",
       "      <td>10591.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Thrifty Parts and Sales</td>\n",
       "      <td>1141.58</td>\n",
       "      <td>3175.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Vigorous Sports Store</td>\n",
       "      <td>1059.31</td>\n",
       "      <td>1043.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Good Toys</td>\n",
       "      <td>880.35</td>\n",
       "      <td>2050.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Transport Bikes</td>\n",
       "      <td>602.19</td>\n",
       "      <td>13301.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Channel Outlet</td>\n",
       "      <td>550.39</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Futuristic Bikes</td>\n",
       "      <td>246.74</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>The Bicycle Accessories Company</td>\n",
       "      <td>106.54</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>West Side Mart</td>\n",
       "      <td>78.81</td>\n",
       "      <td>317.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Essential Bike Works</td>\n",
       "      <td>40.90</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        CompanyName   SubTotal  sum(Weight)\n",
       "0        Action Bicycle Specialists  108561.83   1133911.56\n",
       "1       Metropolitan Bicycle Supply   98278.69    679588.02\n",
       "2               Bulk Discount Store   88812.86     34813.05\n",
       "3         Eastside Department Store   83858.43    565638.72\n",
       "4                     Riding Cycles   78029.69    504095.33\n",
       "5                  Many Bikes Store   74058.81    744328.60\n",
       "6     Instruments and Parts Company   63980.99    731576.77\n",
       "7           Extreme Riding Supplies   57634.63    589939.11\n",
       "8               Trailblazing Sports   41622.05    234328.12\n",
       "9    Professional Sales and Service   39785.33    396843.63\n",
       "10                Nearby Cycle Shop   38418.69    547260.47\n",
       "11            Closest Bicycle Store   35775.21    340144.28\n",
       "12             Thrilling Bike Tours   13823.71    191855.76\n",
       "13      Paints and Solvents Company   12685.89    122609.42\n",
       "14            Remarkable Bike Store    6634.30     45103.50\n",
       "15          Engineered Bike Systems    3398.17     37420.66\n",
       "16            Sports Products Store    3324.28     53389.08\n",
       "17                   Discount Tours    2980.79     14977.56\n",
       "18                     Sports Store    2453.76     38354.65\n",
       "19           Coalition Bike Company    2415.67     29183.00\n",
       "20         Aerobic Exercise Company    2137.23      6770.44\n",
       "21      Tachometers and Accessories    2016.34     10591.33\n",
       "22          Thrifty Parts and Sales    1141.58      3175.14\n",
       "23            Vigorous Sports Store    1059.31      1043.26\n",
       "24                        Good Toys     880.35      2050.23\n",
       "25                  Transport Bikes     602.19     13301.08\n",
       "26                   Channel Outlet     550.39         0.00\n",
       "27                 Futuristic Bikes     246.74         0.00\n",
       "28  The Bicycle Accessories Company     106.54         0.00\n",
       "29                   West Side Mart      78.81       317.00\n",
       "30             Essential Bike Works      40.90         0.00"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a = (order_head.merge(order_det, on='SalesOrderID')\n",
    "#      .merge(product, on='ProductID')\n",
    "#      .merge(cust_aw, on='CustomerID'))\n",
    "# a['Weight'] = a['Weight'].fillna(0) * a['OrderQty'].fillna(0)\n",
    "# (a.groupby(['CompanyName', 'SubTotal'])['Weight'].sum()\n",
    "#  .reset_index().sort_values('SubTotal', ascending=False))\n",
    "\n",
    "(order_head.join(order_det, on='SalesOrderID')\n",
    " .join(product, on='ProductID')\n",
    " .join(cust_aw, on='CustomerID')\n",
    " .withColumn('Weight', col('Weight') * col('OrderQty'))\n",
    " .fillna(0, subset=['Weight'])\n",
    " .groupBy('CompanyName', 'SubTotal')\n",
    " .sum('Weight')\n",
    " .orderBy(col('SubTotal').desc())\n",
    " .toPandas()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "How many products in ProductCategory 'Cranksets' have been sold to an address in 'London'?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0      2"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(product.join(prod_cat.filter(col('name')=='Cranksets'), \n",
    "               on='ProductCategoryID')\n",
    " .join(order_det, on='ProductID')\n",
    " .join(order_head, on='SalesOrderID')\n",
    " .join(cust_aw, on='CustomerID')\n",
    " .join(cust_addr, on='CustomerID')\n",
    " .join(addr.filter(addr['City']=='London'), on='AddressID')\n",
    " .groupBy()\n",
    " .count()\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
